ORDER BY와 LIMIT을 위한 인덱스 설계

ORDER BY와 LIMIT을 위한 인덱스 설계

한눈에 보기

최신 20건처럼 작은 top-N 결과를 조회할 때는 조건을 찾는 것만큼 정렬된 순서로 바로 읽고 일찍 멈출 수 있는가가 중요하다. 등호 조건 뒤에 ORDER BY 열을 배치하고 동률을 깨는 키까지 포함하면 별도 정렬과 불필요한 스캔을 줄일 수 있다. 하지만 중간의 범위·IN 조건, 서로 섞인 정렬 방향, 인덱스에 없는 필터는 조기 종료를 방해할 수 있다.

목차

문제가 되는 상황

블로그 작성자의 최신 글 20개를 조회하는 API가 있다고 하자.

SELECT id, title, created_at
FROM posts
WHERE author_id = 42
ORDER BY created_at DESC
LIMIT 20;

author_id에 단일 인덱스를 만들면 특정 작성자의 글은 빠르게 찾을 수 있다.

CREATE INDEX ix_posts_author
ON posts(author_id);

하지만 작성자 42가 글을 50만 개 가지고 있다면 DB는 후보 50만 건을 찾은 뒤 created_at으로 정렬하고 그중 20개만 반환해야 할 수 있다. 최종 결과가 작다고 작업량까지 작은 것은 아니다.

author_id=42 후보 500,000건
→ created_at DESC 정렬
→ 앞의 20건 반환

이 쿼리는 WHERE뿐 아니라 ORDER BY와 LIMIT을 함께 보며 인덱스를 설계해야 한다.

WHERE만 빠르고 정렬은 느린 쿼리

인덱스 (author_id)는 같은 작성자의 엔트리를 한 구간으로 모아 준다. 하지만 그 구간이 created_at 순으로 정렬되어 있다는 보장은 없다.

(author_id=42, PK=901)
(author_id=42, PK=120)
(author_id=42, PK=887)
...

DB는 각 행에서 created_at을 읽어 정렬 작업을 수행해야 한다. 반면 다음 복합 인덱스는 작성자별로 생성 시각 순서를 만든다.

CREATE INDEX ix_posts_author_created
ON posts(author_id, created_at DESC);

(42, 2026-09-02)
(42, 2026-09-01)
(42, 2026-08-31)
...

author_id = 42로 첫 열이 고정되면 남은 구간은 created_at 내림차순이다. DB는 해당 구간의 시작에서 20개를 읽고 멈출 수 있다.

Top-N 쿼리에서 LIMIT의 진짜 가치

LIMIT의 가치는 응답 행 수를 줄이는 것만이 아니다. 접근 순서가 결과 순서와 같다면 실행 자체를 일찍 끝낼 수 있다.

flowchart LR
    A[인덱스에서 author=42 시작점 탐색] --> B[최신순 엔트리 읽기]
    B --> C{20건 확보?}
    C -->|아니오| B
    C -->|예| D[즉시 종료]

이를 top-N optimization 관점으로 볼 수 있다. 전체 후보를 모두 정렬한 뒤 N개를 자르는 방식과, 이미 정렬된 인덱스에서 N개만 읽는 방식의 차이다.

방식 후보 50만 건일 때 개념적 작업
author 단일 인덱스 + 별도 정렬 많은 후보 읽기 → 정렬 → 20건
author, created 복합 인덱스 시작 위치 탐색 → 20건 읽기 → 종료

물론 SELECT 열이 인덱스에 없으면 20건의 본 테이블 lookup은 필요할 수 있다. 그래도 후보 수십만 건을 읽고 정렬하는 것보다 작은 작업이 될 가능성이 높다.

등호 조건 뒤에 정렬 열을 배치한다

공개된 글만 최신순으로 조회한다고 하자.

SELECT id, title, created_at
FROM posts
WHERE author_id = 42
  AND visibility = 'PUBLIC'
ORDER BY created_at DESC
LIMIT 20;

직관적인 후보는 다음이다.

CREATE INDEX ix_posts_author_visibility_created
ON posts(author_id, visibility, created_at DESC);

앞의 author_id, visibility가 모두 등호로 고정되면 그 구간 안에서 created_at 순서가 그대로 남는다.

(42, PUBLIC, 2026-09-02)
(42, PUBLIC, 2026-09-01)
(42, PUBLIC, 2026-08-30)

WHERE 절에 적은 순서가 아니라 인덱스 정의와 연산 종류가 중요하다. 다음처럼 조건 순서가 바뀌어도 논리적으로 같다.

WHERE visibility = 'PUBLIC'
  AND author_id = 42

반면 visibility 조건이 없는 작성자 전체 목록도 중요하다면 같은 인덱스는 created_at 전체 순서를 바로 제공하지 못한다. 작성자 42 안에서 visibility별로 날짜 순서가 나뉘기 때문이다.

(42, PRIVATE, dates...)
(42, PUBLIC, dates...)

이 쿼리를 위해 (author_id, created_at) 인덱스가 별도로 필요한지 호출 빈도와 쓰기 비용을 비교해야 한다.

동률을 깨는 열이 필요한 이유

created_at만 정렬하면 같은 시각을 가진 여러 행의 상대 순서가 정의되지 않는다.

id=101, created_at=2026-09-02 10:00:00
id=102, created_at=2026-09-02 10:00:00
id=103, created_at=2026-09-02 10:00:00

OFFSET 페이지네이션에서는 실행할 때마다 동률 행의 순서가 달라져 중복이나 누락처럼 보일 수 있다. 커서 페이지네이션에서는 created_at만 커서로 사용하면 같은 시각의 나머지 행을 건너뛸 수 있다.

고유하고 안정적인 id를 보조 정렬 키로 넣는다.

SELECT id, title, created_at
FROM posts
WHERE author_id = :author_id
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX ix_posts_author_created_id
ON posts(author_id, created_at DESC, id DESC);

정렬 기준과 인덱스, 커서 조건은 같은 튜플을 사용한다.

WHERE author_id = :author_id
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
정렬 키는 안정적이어야 한다

페이지를 넘기는 사이에 값이 자주 바뀌는 열을 커서로 쓰면 행이 앞뒤로 이동한다. 생성 시각과 불변 ID처럼 안정적인 키를 선택한다.

ASC와 DESC 방향은 어떻게 맞출까

단일 열 정렬은 DB가 인덱스를 정방향 또는 역방향으로 읽어 처리할 수 있는 경우가 많다.

INDEX (author_id, created_at)

ORDER BY created_at ASC
ORDER BY created_at DESC

하지만 여러 열의 방향이 섞이면 단순 역방향 스캔만으로 맞지 않을 수 있다.

ORDER BY created_at DESC, id ASC

정방향 스캔이 (created_at ASC, id ASC)이고 전체 역방향이 (created_at DESC, id DESC)라면 (DESC, ASC) 혼합은 별도의 descending key 정의가 필요할 수 있다.

CREATE INDEX ix_posts_author_created_desc_id_asc
ON posts(author_id, created_at DESC, id ASC);

DB와 버전별로 내림차순 인덱스 지원과 실행 계획 표현이 다르므로 실제 환경에서 정렬 단계가 사라지는지 확인한다. UI 요구를 바꿀 수 있다면 혼합 방향보다 일관된 방향의 고유 튜플이 구현과 검증에 단순하다.

범위 조건이 ORDER BY보다 앞에 있을 때

다음 인덱스와 쿼리를 보자.

CREATE INDEX ix_posts_author_score_created
ON posts(author_id, score, created_at DESC);

SELECT id, created_at
FROM posts
WHERE author_id = 42
  AND score >= 80
ORDER BY created_at DESC
LIMIT 20;

author는 등호지만 score는 범위다. score 값별로 created_at이 정렬되어 있을 뿐, score >= 80 전체가 created_at 하나의 순서로 놓이지 않는다.

score=80: dates...
score=81: dates...
score=82: dates...

따라서 인덱스로 score 범위는 찾을 수 있어도 전체 결과의 created_at 정렬을 바로 얻기는 어렵다. created_at을 score보다 앞에 두면 최신순으로 읽으며 score를 필터링할 수 있다.

INDEX (author_id, created_at DESC, score)

어느 쪽이 나은지는 선택도와 LIMIT에 달려 있다.

인덱스 순서는 “WHERE 열이 ORDER BY 열보다 먼저”라는 고정 공식이 아니라 읽을 엔트리 수와 조기 종료 가능성을 비교해 정한다.

IN 조건과 여러 인덱스 구간

여러 카테고리의 최신 글을 조회한다고 하자.

SELECT id, category, created_at
FROM posts
WHERE author_id = 42
  AND category IN ('DATABASE', 'BACKEND')
ORDER BY created_at DESC
LIMIT 20;

(author_id, category, created_at)는 두 category 범위를 각각 최신순으로 읽을 수 있다. 하지만 두 범위를 합친 전체 최신순을 만들려면 병합 또는 정렬이 필요할 수 있다.

DATABASE: 09-02, 08-30, 08-20 ...
BACKEND:  09-01, 08-31, 08-25 ...
→ 전체 최신순으로 merge

(author_id, created_at, category)는 최신 글부터 읽으며 category를 필터링할 수 있다. 선택한 두 카테고리의 비율이 높으면 LIMIT을 빨리 채우지만, 드물다면 많은 엔트리를 읽고 버린다. 실제 분포를 기준으로 두 계획을 비교해야 한다.

필터가 뒤늦게 적용되면 LIMIT만큼만 읽지 못한다

인덱스가 ORDER BY를 지원하더라도 추가 필터가 인덱스에 없으면 DB는 행을 읽은 뒤 조건을 검사한다.

INDEX (author_id, created_at DESC)

SELECT id, title, created_at
FROM posts
WHERE author_id = 42
  AND is_featured = true
ORDER BY created_at DESC
LIMIT 20;

작성자의 최신 글 중 featured가 1%뿐이라면 결과 20건을 얻기 위해 약 2,000건 이상을 읽을 수 있다. LIMIT은 반환 건수이지 읽기 건수를 보장하지 않는다.

index entries read: 2,314
rows passing filter: 20
rows returned:       20

featured 목록이 핵심 쿼리라면 (author_id, is_featured, created_at)을 검토할 수 있다. 그러나 인덱스 추가 비용과 실제 호출 빈도를 함께 봐야 한다. 작은 결과 열까지 포함해 lookup을 줄이는 방식은 Covering Index로 테이블 접근 줄이기와 연결된다.

OFFSET과 Cursor pagination의 접근 차이

인덱스가 정렬을 지원해도 큰 OFFSET은 앞의 엔트리를 읽고 버려야 한다.

SELECT id, title, created_at
FROM posts
WHERE author_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

개념적으로 100,020개를 순서대로 지나간 뒤 20개를 반환한다. 반면 커서는 마지막 튜플 다음 위치부터 탐색한다.

SELECT id, title, created_at
FROM posts
WHERE author_id = 42
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

flowchart LR
    A[커서 튜플로 B+Tree 위치 탐색] --> B[다음 20개 순차 읽기]
    C[OFFSET 100000] --> D[앞 100000개 읽고 버리기]
    D --> E[다음 20개]

커서 방식은 임의의 페이지 번호로 바로 이동하기 어렵고 정렬 키 설계가 필요하지만 깊은 페이지에서도 읽기 양이 안정적이다. 자세한 API 설계 비교는 Offset pagination과 Cursor pagination 비교에서 다룬다.

filesort는 항상 디스크 정렬이라는 뜻이 아니다

MySQL의 실행 계획에 Using filesort가 보이면 무조건 디스크 파일을 쓴다고 오해하기 쉽다. 이 용어는 대체로 인덱스 순서만으로 정렬을 끝내지 못해 별도 정렬 알고리즘을 사용한다는 뜻이며, 데이터 크기와 메모리 설정에 따라 메모리에서 끝날 수도 있고 디스크 임시 공간을 사용할 수도 있다.

별도 정렬도 후보가 50건이면 충분히 빠를 수 있다. 선택도가 높은 WHERE 인덱스로 먼저 크게 줄인 뒤 작은 결과를 정렬하는 계획이, 정렬 인덱스를 따라 수백만 건을 스캔하며 필터링하는 계획보다 나을 수 있다.

filesort 제거 자체를 목표로 삼지 않는다

전체 실행 시간, 실제 읽은 행 수, sort rows와 메모리·디스크 사용을 함께 본다. 정렬 표시를 없애기 위해 더 나쁜 인덱스를 강제하면 안 된다.

모든 정렬 옵션에 인덱스를 만들 수는 없다

상품 목록에 최신순, 가격 낮은순, 평점순, 판매량순이 모두 있다면 각 정렬 조합마다 인덱스를 만들고 싶어진다.

(category_id, created_at)
(category_id, price)
(category_id, rating)
(category_id, sales_count)

여기에 필터 조합까지 곱하면 인덱스 수가 폭발한다. 모든 쓰기와 저장 공간에 비용이 생기므로 다음 기준으로 우선순위를 정한다.

핵심 경로 두세 개는 인덱스로 지원하고 드문 정렬은 작은 후보를 정렬하게 두는 것이 전체 시스템에는 더 나을 수 있다. 인덱스가 많아질 때의 비용은 인덱스가 많아질수록 쓰기가 느려지는 이유와 이어진다.

실행 계획으로 조기 종료를 확인한다

인덱스 생성 전후로 실제 실행을 비교한다.

EXPLAIN ANALYZE
SELECT id, title, created_at
FROM posts
WHERE author_id = 42
  AND visibility = 'PUBLIC'
ORDER BY created_at DESC, id DESC
LIMIT 20;

다음 질문에 답한다.

좋은 top-N 계획의 기대 형태
Index range scan on ix_posts_author_visibility_created_id
  actual rows ≈ 20
Limit: 20

실행 계획 출력 형식은 DB와 버전에 따라 다르므로 특정 문자열보다 연산 순서와 estimated/actual rows를 읽는다. 데이터도 author별 글 수, visibility 비율, created_at 동률을 운영과 비슷하게 만들어야 한다.

결론

ORDER BY와 LIMIT 쿼리의 인덱스는 후보를 찾는 것뿐 아니라 원하는 순서로 읽고 필요한 개수에서 멈추게 하는 구조여야 한다. 앞의 등호 조건을 고정한 뒤 정렬 열과 동률을 깨는 고유 키를 이어 두면 대규모 후보 정렬과 깊은 페이지 탐색을 줄일 수 있다.

그러나 범위와 IN 조건이 정렬 열 앞에 있으면 전체 결과 순서가 끊길 수 있고, 뒤늦은 필터 때문에 LIMIT보다 훨씬 많은 엔트리를 읽을 수 있다. filesort 표시를 제거하는 것 자체보다 실제 읽은 행 수와 정렬 비용, 조기 종료 여부를 본다. 모든 정렬 옵션에 인덱스를 만드는 대신 핵심 top-N 경로를 우선하고, 깊은 페이지에는 안정적인 복합 커서를 사용하는 것이 읽기 성능과 쓰기 비용 사이의 현실적인 균형이다.

관련 노트